import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.gargoylesoftware.htmlunit.BrowserVersion;
import com.gargoylesoftware.htmlunit.FailingHttpStatusCodeException;
import com.gargoylesoftware.htmlunit.WebClient;
import com.gargoylesoftware.htmlunit.html.DomElement;
import com.gargoylesoftware.htmlunit.html.HtmlElement;
import com.gargoylesoftware.htmlunit.html.HtmlPage;

import uk.ac.shef.wit.simmetrics.similaritymetrics.AbstractStringMetric;
import uk.ac.shef.wit.simmetrics.similaritymetrics.JaroWinkler;

// ezerjo letöltése .xls fájlba
// összehasonlítás sajáttal
// évjárat tisztázás
// évjárat attribútum hozzáadása
// fehér, rozé stb attribútum hozzáadása

public class Scanner {

	public static final String DB_HOST = "localhost";// 91.227.139.141,

	// localhost

	public static final int DB_PORT = 3306;

	public static final String DB_USER = "vincell_web";// vincell_web, root;

	public static final String DB_PASSWORD = "freebex08";// freebex08, domike

	public static void main(String[] args) {
		 new Scanner().saveEzerjoToXls();
		// new Scanner().synchronizeProducts();
		// new Scanner().writeEnabledBackToVincell();
//		 new Scanner().writeIdBackToVincell();
		// new Scanner().writeEvjaratBackToVincell();

		new Scanner().checkEzerjoProductsInVincell();
	}

	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection("jdbc:mysql://" + DB_HOST + ":"
				+ DB_PORT
				+ "/vincell_web?useUnicode=yes&characterEncoding=UTF-8&user="
				+ DB_USER + "&password=" + DB_PASSWORD);
	}

	private void saveEzerjoToXls() {
		try {
			Workbook wb = new HSSFWorkbook();
			FileOutputStream fileOut = new FileOutputStream("ezerjo.xls");
			Sheet sheet = wb.createSheet("new sheet");
			int rowNumber = 0;

			final WebClient webClient = new WebClient(BrowserVersion.CHROME_16/*,
					"10.137.1.16", 3128*/);

			final HtmlPage page = webClient.getPage("http://www.ezerjo.hu");
			final DomElement menuListDiv = page
					.getElementById("block-menu-menu-borvidekek");

			final List<String> boraszatPageList = new ArrayList<String>();
			for (HtmlElement borvidekekAnchor : menuListDiv
					.getElementsByTagName("a")) {
				final HtmlPage videkPage = webClient
						.getPage("http://www.ezerjo.hu"
								+ borvidekekAnchor.getAttribute("href"));

				for (Object object : videkPage
						.getByXPath("//div[@id='main']/div/div/div[@class='view-content']/table/tbody/tr/td")) {
					HtmlElement videkElement = (HtmlElement) object;

					HtmlElement nameElement = (HtmlElement) videkElement
							.getFirstByXPath("div[@class='views-field views-field-name']/span");
					if (nameElement != null) {
						String _name = nameElement.asText().trim();
						System.out.println("--------------------" + _name);

						HtmlElement linkElement = (HtmlElement) videkElement
								.getFirstByXPath("div[@class='views-field views-field-nothing']/span/a");

						try {
							final HtmlPage listaPage = webClient
									.getPage("http://www.ezerjo.hu"
											+ linkElement.getAttribute("href"));

							for (Object object2 : listaPage
									.getByXPath("//body/div/div/div[@id='main']/div/div/div[@class='view-content']/table/tbody/tr/td")) {
								HtmlElement listaElement = (HtmlElement) object2;

								HtmlElement productElement = (HtmlElement) listaElement
										.getFirstByXPath("div[@class='views-field views-field-title']/span/a");
								if (productElement != null) {
									HtmlElement evjaratElement = (HtmlElement) listaElement
											.getFirstByXPath("div[@class='views-field views-field-field-evj']/div");
									HtmlElement priceElement = (HtmlElement) listaElement
											.getFirstByXPath("div[@class='views-field views-field-display-price']/span");

									String url = "http://www.ezerjo.hu"
											+ productElement
													.getAttribute("href");
									// download prodcut page
									final HtmlPage productDetailsPage = webClient
											.getPage(url);
									List<DomElement> list = productDetailsPage
											.getElementsByTagName("article");
									HtmlElement productIdElement = (HtmlElement) list
											.get(0);
									String idClass = productIdElement
											.getAttribute("class");

									String id = null;
									Pattern p = Pattern.compile("\\d+");
									Matcher m = p.matcher(idClass);
									if (m.find()) {
										id = m.group();

									} else {
										throw new Exception(
												"Baj van. nincs meg az ID. "
														+ productElement
																.getAttribute("href"));
									}

									// ---------

									System.out.println("--------------------");
									System.out.println("borasz: " + _name);
									System.out.println("termek: "
											+ productElement.asText().trim()
											+ "\t\t\t" + id);
									if (evjaratElement != null) {
										System.out.println("evjarat: "
												+ evjaratElement.asText()
														.trim());
									}
									String price = priceElement.asText().trim()
											.replace(".", "").replace(",", "")
											.replace("-", "");
									System.out.println("ar: " + price);

									Row row = sheet.createRow(rowNumber);
									row.createCell(0).setCellValue(_name);
									row.createCell(1).setCellValue(id);
									row.createCell(2).setCellValue(
											productElement.asText().trim());
									if (evjaratElement != null) {
										row.createCell(3).setCellValue(
												evjaratElement.asText().trim());
									}
									row.createCell(4).setCellValue(price);
									row.createCell(5).setCellValue(url);
									rowNumber++;
								}
							}
						} catch (FailingHttpStatusCodeException e) {
							System.out.println("http://www.ezerjo.hu"
									+ linkElement.getAttribute("href"));
							e.printStackTrace();
						}
					}
				}

				// / final HtmlDivision div = (HtmlDivision)

				// videkPage.getElementByAccessKey(accessKey)
				// boraszatPageList.add
			}

			// end
			wb.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * ezerjo_id szerint kikeressül a Vincell DB-ből a terméket és elérhetőre
	 * állítjuk. Közben List-ben tárolku, hogy melyiket érte el. Amelyiekt nem,
	 * azt leltiltom. Ha új van, akkor azokat email-ben elküldöm magamnak.
	 */
	private void checkEzerjoProductsInVincell() {
		try {
			List<Integer> processed = new ArrayList<Integer>();
			List<EzerjoProduct> notFound = new ArrayList<EzerjoProduct>();
			List<EzerjoProduct> arHiba = new ArrayList<EzerjoProduct>();

			Connection connection = Scanner.getConnection();
			connection.setAutoCommit(false);
			
			final PreparedStatement updateProductStatement = connection
					.prepareStatement("update oc_product set status = true where ezerjo_id = ?");
			
			final PreparedStatement selectProduct = connection
					.prepareStatement("select product_id, price from oc_product where ezerjo_id = ?");
			
			final PreparedStatement updateProductEvjarat = connection
					.prepareStatement("update oc_product_attribute set text = ? where product_id = ? and attribute_id = 12");
			
			final PreparedStatement insertProductEvjarat = connection
					.prepareStatement("insert into oc_product_attribute set product_id = ?, attribute_id = ?, language_id = ?, text = ?");

			NPOIFSFileSystem ezerjoFs = new NPOIFSFileSystem(new File(
					"ezerjo.xls"));
			HSSFWorkbook ezerjoWb = new HSSFWorkbook(ezerjoFs.getRoot(), true);
			HSSFSheet ezerjoSheet = ezerjoWb.getSheet("new sheet");

			for (int i = 0; i < ezerjoSheet.getLastRowNum(); i++) {
				Row row = ezerjoSheet.getRow(i);

				String ezerjoId = null;
				if (row.getCell(1) != null) {
					if (row.getCell(1).getCellType() == Cell.CELL_TYPE_NUMERIC) {
						ezerjoId = Double.toString(row.getCell(1)
								.getNumericCellValue());
					} else {
						ezerjoId = row.getCell(1).getStringCellValue();
					}
				}
				int eId = Double.valueOf(ezerjoId).intValue();
				String evjarat = null;
				if (row.getCell(3) != null) {
					if (row.getCell(3).getCellType() == Cell.CELL_TYPE_NUMERIC) {
						evjarat = Double.toString(row.getCell(3)
								.getNumericCellValue());
					} else {
						evjarat = row.getCell(3).getStringCellValue();
					}
				}
				
				BigDecimal ezerjoAr = new BigDecimal(row.getCell(4).getStringCellValue());
				
				if(evjarat != null && evjarat.trim().length() > 0) {
					updateProductStatement.setInt(1, eId);
					int count = 0;
					count = updateProductStatement.executeUpdate();																						
									
					EzerjoProduct ep = new EzerjoProduct();
					ep.setEid(ezerjoId);
					ep.setName(row.getCell(2).getStringCellValue());
					ep.setBorasz(row.getCell(0).getStringCellValue());
					ep.setUrl(row.getCell(5).getStringCellValue());
					
					if (count == 0) {
						notFound.add(ep);
	
					} else if (count == 1) {
						processed.add(eId);
						
						selectProduct.setInt(1, eId);
						ResultSet rs = selectProduct.executeQuery();
						if (rs.next()) {
							int productId = rs.getInt("product_id");
							BigDecimal vincellPrice = rs.getBigDecimal("price");
							
							BigDecimal arArany = vincellPrice.divide(ezerjoAr, RoundingMode.HALF_UP);
							ep.setArArany(arArany);
							
							if(arArany.compareTo(new BigDecimal("0.88")) < 0 || arArany.compareTo(new BigDecimal("1.1")) > 0) {
								arHiba.add(ep);
							}														
							
							updateProductEvjarat.setString(1, evjarat);														
							updateProductEvjarat.setInt(2, productId);
							int evjCount = updateProductEvjarat.executeUpdate();
							if(evjCount == 0) {
								insertProductEvjarat.setInt(1, productId);
								insertProductEvjarat.setInt(2, 12);
								insertProductEvjarat.setInt(3, 1);
								insertProductEvjarat.setString(4, evjarat);
								insertProductEvjarat.executeUpdate();
							}							
						}
	
					} else if (count > 1) {
						// sendmail - with error
						SendEmail.send("Vincell - Ezerjo ID error", "Ezerjo ID: "
								+ eId + "  " + count + "-times.");
						System.exit(0);
					}
				}
			}

			// disable not processed
			Statement st = connection
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = st.executeQuery("select * from oc_product");
			while (rs.next()) {
				if (!processed.contains(rs.getInt("ezerjo_id"))) {
					rs.updateBoolean("status", false);
					rs.updateRow();
				}
			}

			// send not founds
			// sendmail - notFound

			if (!notFound.isEmpty()) {
				StringBuffer sb = new StringBuffer();

				for (EzerjoProduct ep : notFound) {
					sb.append(ep.getEid() + "\t\t" + ep.getBorasz() + "\t\t"
							+ ep.getName() + "\t\t" + ep.getUrl());
					sb.append("\n");
				}

				SendEmail.send("Vincell - Daily check: " + notFound.size(),
						sb.toString());
			}
			
			if (!arHiba.isEmpty()) {
				StringBuffer sb = new StringBuffer();

				for (EzerjoProduct ep : arHiba) {
					sb.append(ep.getEid() + "\t\t" + ep.getBorasz() + "\t\t"
							+ ep.getName() + "\t\t" + ep.getArArany().toPlainString() + "\t\t" + ep.getUrl());
					sb.append("\n");
				}

				SendEmail.send("Vincell - Price check: " + arHiba.size(),
						sb.toString());
			}
						

			connection.commit();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// kiirom az összes sort.
	// bor vincell id + vincell borász + vincell bor név + párosított név (vagy
	// üres ha nincs pár, vagy X ha több is van) - vincell évjárat - ezerjo
	// évjárat - vincel ár -ezetjó ár

	private void synchronizeProducts() {
		try {
			Workbook syncWb = new HSSFWorkbook();
			FileOutputStream syncFileOut = new FileOutputStream("sync.xls");
			Sheet syncSheet = syncWb.createSheet("new sheet");
			Row _row = syncSheet.createRow(0);
			_row.createCell(0).setCellValue("v id");
			_row.createCell(1).setCellValue("ezerjo id");
			_row.createCell(2).setCellValue("v borász");
			_row.createCell(3).setCellValue("ezerjo borász");
			_row.createCell(4).setCellValue("v bor");
			_row.createCell(5).setCellValue("ezerjo bor");
			_row.createCell(6).setCellValue("v evjarat");
			_row.createCell(7).setCellValue("ezerjo evjarat");
			_row.createCell(8).setCellValue("v ar");
			_row.createCell(9).setCellValue("ezerjo ar");

			CellStyle styleYellow = syncWb.createCellStyle();
			styleYellow.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
			// styleYellow.setFillPattern(CellStyle.BIG_SPOTS);

			// ------

			NPOIFSFileSystem ezerjoFs = new NPOIFSFileSystem(new File(
					"ezerjo.xls"));
			HSSFWorkbook ezerjoWb = new HSSFWorkbook(ezerjoFs.getRoot(), true);
			HSSFSheet ezerjoSheet = ezerjoWb.getSheet("new sheet");

			NPOIFSFileSystem vincellFs = new NPOIFSFileSystem(new File(
					"test.xls"));
			HSSFWorkbook vincellWb = new HSSFWorkbook(vincellFs.getRoot(), true);
			HSSFSheet vincellSheet = vincellWb.getSheet("Products");

			int sameName = 0;

			for (int i = 1; i < vincellSheet.getLastRowNum(); i++) {
				// for (int i = 1; i < Math.min(100,
				// ezerjoSheet.getLastRowNum()); i++) {
				Row row = vincellSheet.getRow(i);
				String vBorasz = row.getCell(12).getStringCellValue();
				String vProduct = row.getCell(1).getStringCellValue();
				String vEvjarat = row.getCell(32).getStringCellValue();
				double vAr = row.getCell(17).getNumericCellValue();

				Object[] result = findSimilar(ezerjoSheet, vBorasz, vProduct,
						vEvjarat, vAr);

				Row similarRow = (Row) result[0];
				Float similarity = (Float) result[1];

				Row syncRow = syncSheet.createRow(i);
				syncRow.createCell(0).setCellValue(
						row.getCell(0).getNumericCellValue());
				syncRow.createCell(2).setCellValue(vBorasz);
				Cell cell3 = syncRow.createCell(4);
				cell3.setCellValue(vProduct);
				if (similarity.compareTo(new Float(1)) == 0) {
					cell3.setCellStyle(styleYellow);
				}

				Cell cell5 = syncRow.createCell(6);
				if (similarity.compareTo(new Float(1)) == 0) {
					cell5.setCellStyle(styleYellow);
				}
				// .setCellValue(vEvjarat);
				syncRow.createCell(8).setCellValue(vAr * 1.27);

				syncRow.createCell(10).setCellValue(similarity);

				if (similarRow != null) {
					sameName++;

					syncRow.createCell(1).setCellValue(
							similarRow.getCell(1).getStringCellValue());
					Cell cell1 = syncRow.createCell(3);
					cell1.setCellValue(similarRow.getCell(0)
							.getStringCellValue());
					// cell1.setCellStyle(styleYellow);
					syncRow.createCell(5).setCellValue(
							similarRow.getCell(2).getStringCellValue());
					if (similarRow.getCell(3) != null) {
						syncRow.createCell(7).setCellValue(
								similarRow.getCell(3).getStringCellValue());
					}
					syncRow.createCell(9).setCellValue(
							similarRow.getCell(4).getStringCellValue());
					// System.out.println(eProduct + "\t\t" +
					// similarRow.getCell(1).getStringCellValue());
				}
			}

			System.out.println("------------------------");
			System.out.println("sameName: " + sameName);

			// end
			syncWb.write(syncFileOut);
			syncFileOut.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private Object[] findSimilar(HSSFSheet ezerjoSheet, String vBorasz,
			String vProduct, String vEvjarat, double vAr) {
		Object[] result = new Object[2];

		result[0] = null;
		result[1] = new Float(0);

		// System.out.println("------- " + vBorasz + "\t\t\t" + vProduct +
		// " ----------------------------------------- ");
		System.out.println("------- " + vBorasz
				+ " ------------------------------------------------- ");
		float max = 0;
		Row rowMax = null;
		for (int i = 0; i < ezerjoSheet.getLastRowNum(); i++) {
			Row row = ezerjoSheet.getRow(i);
			String eBorasz = row.getCell(0).getStringCellValue();
			String eProduct = row.getCell(2).getStringCellValue();
			String eEvjarat = null;
			if (row.getCell(3) != null) {
				eEvjarat = row.getCell(3).getStringCellValue();
			}
			String eAr = row.getCell(4).getStringCellValue();

			AbstractStringMetric metric = new JaroWinkler();
			float _result = metric.getSimilarity(vBorasz, eBorasz);
			if (_result > 0.9) {

				AbstractStringMetric metric2 = new JaroWinkler();
				float result2 = metric2.getSimilarity(vProduct, eProduct);

				if (max < result2) {
					max = result2;

					if (result2 > 0.9) {
						System.out.println("\t" + result2 + "\t" + vProduct
								+ "\t\t\t" + eProduct);
						rowMax = row;
					}
				}
			}
		}

		result[0] = rowMax;
		result[1] = new Float(max);

		return result;
	}

	private void writeEnabledBackToVincell() {
		try {
			File file = new File("test.xls");

			Workbook wb = WorkbookFactory.create(new FileInputStream(file));
			Sheet sheet = wb.getSheet("Products");

			for (int i = 1; i < sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				Cell cell = row.createCell(28);
				cell.setCellValue("false");
			}

			Workbook wbSync = WorkbookFactory.create(new FileInputStream(
					"sync.xls"));
			Sheet sheetSync = wbSync.getSheetAt(0);

			for (int j = 1; j < sheetSync.getLastRowNum(); j++) {
				Row rowSync = sheetSync.getRow(j);

				String vincellId = Double.toString(rowSync.getCell(0)
						.getNumericCellValue());
				String ezerjoId = null;
				if (rowSync.getCell(1) != null) {
					if (rowSync.getCell(1).getCellType() == Cell.CELL_TYPE_NUMERIC) {
						ezerjoId = Double.toString(rowSync.getCell(1)
								.getNumericCellValue());
					} else {
						ezerjoId = rowSync.getCell(1).getStringCellValue();
					}
				}

				if (ezerjoId != null && ezerjoId.trim().length() > 0) {
					for (int i = 1; i < sheet.getLastRowNum(); i++) {
						Row row = sheet.getRow(i);
						String vincellId2 = Double.toString(row.getCell(0)
								.getNumericCellValue());

						if (vincellId2.equals(vincellId)) {
							row.getCell(28).setCellValue("true");
						}
					}
				}
			}

			FileOutputStream fileOut = new FileOutputStream("test2.xls");
			wb.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void writeIdBackToVincell() {
		try {
			File file = new File("test.xls");

			// XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
			Workbook wb = WorkbookFactory.create(file);
			Sheet sheet = wb.getSheet("Products");

			Workbook wbSync = WorkbookFactory.create(new FileInputStream(
					"sync.xls"));
			Sheet sheetSync = wbSync.getSheetAt(0);

			for (int i = 1; i < sheet.getLastRowNum(); i++) {
				// for (int i = 1; i < Math.min(100, sheet.getLastRowNum());
				// i++) {
				Row row = sheet.getRow(i);
				String vincellId = Double.toString(row.getCell(0)
						.getNumericCellValue());
				Cell cellEzerjoId = row.createCell(1);

				for (int j = 1; j < sheetSync.getLastRowNum(); j++) {
					Row rowSync = sheetSync.getRow(j);
					String vincellId2 = Double.toString(rowSync.getCell(0)
							.getNumericCellValue());
					if (vincellId.equals(vincellId2)) {
						if (rowSync.getCell(1) != null) {
							if (rowSync.getCell(1).getCellType() == Cell.CELL_TYPE_NUMERIC) {
								cellEzerjoId.setCellValue(rowSync.getCell(1)
										.getNumericCellValue());
							} else {
								cellEzerjoId.setCellValue(rowSync.getCell(1)
										.getStringCellValue());
							}
						}

						break;
					}
				}
			}

			FileOutputStream fileOut = new FileOutputStream("test2.xls");
			wb.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void writeEvjaratBackToVincell() {
		try {
			Workbook wb = WorkbookFactory
					.create(new FileInputStream("test.xls"));
			Sheet sheet = wb.getSheet("Attributes");

			Workbook wbSync = WorkbookFactory.create(new FileInputStream(
					"sync.xls"));
			Sheet sheetSync = wbSync.getSheetAt(0);

			for (int j = 1; j < sheetSync.getLastRowNum(); j++) {
				Row rowSync = sheetSync.getRow(j);
				String vincellId = Double.toString(rowSync.getCell(0)
						.getNumericCellValue());
				if (rowSync.getCell(7) != null /*
												 * && rowSync.getCell(7).
												 * getStringCellValue
												 * ().trim().length() > 0
												 */) {
					String evjarat = null;

					if (rowSync.getCell(7).getCellType() == Cell.CELL_TYPE_NUMERIC) {
						evjarat = Double.toString(rowSync.getCell(7)
								.getNumericCellValue());
					} else {
						evjarat = rowSync.getCell(7).getStringCellValue();
					}

					if (evjarat != null && evjarat.trim().length() > 0) {
						boolean previousFound = false;
						for (int i = 1; i < sheet.getLastRowNum(); i++) {
							Row row = sheet.getRow(i);

							String vincellId2 = null;
							if (row.getCell(0) != null) {
								if (row.getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC) {
									vincellId2 = Double.toString(row.getCell(0)
											.getNumericCellValue());
								} else {
									vincellId2 = row.getCell(0)
											.getStringCellValue();
								}
							}

							if (vincellId.equals(vincellId2)
									&& row.getCell(3).getStringCellValue()
											.equals("évjárat")) {
								row.createCell(4).setCellValue(evjarat);
								previousFound = true;
								System.out.println("X ");
								break;
							}
						}

						if (!previousFound) {
							Row row = sheet
									.createRow(sheet.getLastRowNum() + 1);
							row.createCell(0).setCellValue(vincellId);
							row.createCell(1).setCellValue(1);
							row.createCell(2).setCellValue("Évjárat");
							row.createCell(3).setCellValue("évjárat");
							row.createCell(4).setCellValue(
									new BigDecimal(evjarat).setScale(0)
											.toString());
							System.out.println("+++ ");
						}
					}
				}
			}

			FileOutputStream fileOut = new FileOutputStream("test2.xls");
			wb.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
